package com.apes.fn.crm.ReturnVisit.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * @program: apes-server
 * @description
 * @author: czs
 * @create: 2021-08-12 16:41
 **/
@Component
public class VisitReportDao extends ApesDao {


    /***
     *
     */
    public JSONArray getVisitStatistics(JSONObject param) {
        Object parse = param.get("parse");
        Object end = param.get("end");
        Object start = param.get("start");
        Object date = param.get("date");
        Object visitor = param.get("visitor");

        StringBuffer sf = new StringBuffer();
        String getRest = "SELECT QTY_SHOULD_VISIT-QTY_FINISH\n" +
                "                    FROM FN_VISIT_STATISTICS V\n" +
                "                    WHERE V.VISITOR_ID\n" +
                "                        = F.VISITOR_ID\n" +
                "                      AND V.INSERT_DATE = (SELECT MAX(S.INSERT_DATE)\n" +
                "                                           FROM FN_VISIT_STATISTICS S\n" +
                "                                           WHERE S.INSERT_DATE <= (TRUNC(to_date('"+start+"','"+parse+"'), '"+date+"') - 1)\n" +
                "                                             AND S.VISITOR_ID = F.VISITOR_ID)";

        String getAdd = "SELECT COUNT(A.ID) ADD_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TO_DATE(TO_CHAR(A.SHOULD_VISIT_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')" +
                " AND TO_DATE(TO_CHAR(A.SHOULD_VISIT_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                " AND A.VISITOR_ID = F.VISITOR_ID";


        String getVisited =  "SELECT SUM(C.VISITED) VISITED\n" +
                "  FROM (SELECT A.VISITOR_ID, COUNT(A.ID) VISITED\n" +
                "           FROM FN_RETURN_VISIT A\n" +
                "          WHERE A.STATE IN ('end', 'finish')\n" +
                "            AND TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')" +
                " AND TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                "          GROUP BY A.VISITOR_ID\n" +
                "         UNION\n" +
                "         SELECT A.VISITOR_ID, COUNT(A.ID) VISITED\n" +
                "           FROM FN_RETURN_VISIT A\n" +
                "          WHERE A.STATE = 'again'\n" +
                "            AND TO_DATE(TO_CHAR(A.NEXT_VISIT_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                "            AND EXISTS\n" +
                "          (SELECT 1\n" +
                "                   FROM FN_VISIT_HISTORY B\n" +
                "                  WHERE A.ID = B.RETURN_VISIT_ID\n" +
                "                    AND TO_DATE(TO_CHAR(B.VISITED_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')" +
                " AND TO_DATE(TO_CHAR(B.VISITED_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                ")GROUP BY A.VISITOR_ID) C\n" +
                " WHERE C.VISITOR_ID = F.VISITOR_ID";

        String getFinish = "SELECT COUNT(A.ID) FINISH_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')" +
                " AND TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                "   AND A.STATE = 'finish'\n" +
                " AND A.VISITOR_ID = F.VISITOR_ID";

        String getEnd = "SELECT COUNT(A.ID) END_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')" +
                " AND TO_DATE(TO_CHAR(A.VISITED_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')" +
                "   AND A.STATE = 'end'\n" +
                "   AND A.VISITOR_ID = F.VISITOR_ID" ;

        sf.append("SELECT Y.QTY_REST,Y.QTY_ADD,Y.QTY_FINISH,Y.QTY_VISITED,Y.QTY_END,Y.VISITED_SUCCESS_RATE,Y.VISITED_RATE,Y.VISITOR,Y.STATISTICS_DATE INSERT_DATE,");
        sf.append("(Y.QTY_REST+Y.QTY_ADD)QTY_SHOULD_VISIT,(CASE WHEN ((Y.QTY_REST+Y.QTY_ADD)-Y.QTY_VISITED)<0 THEN 0 ELSE((Y.QTY_REST+Y.QTY_ADD)-Y.QTY_VISITED) END)QTY_WAIT_VISIT,(Y.QTY_VISITED)QTY_CALL");
        sf.append(" FROM(SELECT X.*,");
        sf.append("     (CASE WHEN X.QTY_VISITED = 0 THEN '0'|| '%' ELSE round(X.QTY_FINISH / X.QTY_VISITED, 2) * 100 || '%' END) VISITED_SUCCESS_RATE,");
        sf.append("     (CASE WHEN (X.QTY_REST + X.QTY_ADD) = 0 or X.QTY_VISITED = 0 THEN '0'|| '%' ELSE round((X.QTY_VISITED / (X.QTY_REST + X.QTY_ADD)), 2) * 100 || '%' END) VISITED_RATE,");
        sf.append(" (SELECT R.NAME FROM MAS_PARTY R WHERE R.ID = X.PARTY_ID)VISITOR");
        sf.append("     FROM (SELECT NVL(("+getRest+"),0)QTY_REST,NVL(("+getAdd+"),0)QTY_ADD ,NVL(("+getVisited+"),0)QTY_VISITED,NVL(("+getFinish+"),0)QTY_FINISH,NVL(("+getEnd+"),0)QTY_END,STATISTICS_DATE,");
        sf.append("     F.VISITOR_ID,");
        sf.append("     (SELECT R.PARTY_ID FROM MAS_PARTY_ROLE R WHERE R.ID = F.VISITOR_ID) PARTY_ID");
        sf.append("            FROM (SELECT TO_CHAR(F.INSERT_DATE, '"+ parse +"') STATISTICS_DATE, F.*");
        sf.append("                  FROM FN_VISIT_STATISTICS F");
        sf.append("                  WHERE 1 = 1" );
        if (visitor!=null)
            sf.append("              AND VISITOR_ID = '"+visitor+"'");

        sf.append("                    AND TO_DATE(TO_CHAR(F.INSERT_DATE, '"+ parse +"'), '"+ parse +"') <= TO_DATE('"+end+"', '"+ parse +"')");
        sf.append("                    AND TO_DATE(TO_CHAR(F.INSERT_DATE, '"+ parse +"'), '"+ parse +"') >= TO_DATE('"+start+"', '"+ parse +"')");
        sf.append("                    AND VISITOR_ID IS NOT NULL");
        sf.append("                    ) F");
        sf.append("            WHERE 1 = 1");

        sf.append("            GROUP BY F.STATISTICS_DATE, F.VISITOR_ID) X) Y ORDER BY Y.STATISTICS_DATE,Y.QTY_ADD desc  ");
        return toJSONArray(find(sf.toString(), param));
    }

}
